{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a09fa2b4",
   "metadata": {},
   "source": [
    "# pandas 使用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acca39f2",
   "metadata": {},
   "source": [
    "### 安装"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "937943fc",
   "metadata": {},
   "source": [
    "```\n",
    "pip install pandas\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e37cbbc4",
   "metadata": {},
   "source": [
    "### pandas 读取表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "328a9231",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>货物编号</th>\n",
       "      <th>货物名称</th>\n",
       "      <th>批号</th>\n",
       "      <th>转换率</th>\n",
       "      <th>单位</th>\n",
       "      <th>期初</th>\n",
       "      <th>常规产品整数</th>\n",
       "      <th>常规零数</th>\n",
       "      <th>常规小计</th>\n",
       "      <th>...</th>\n",
       "      <th>旧货小计</th>\n",
       "      <th>可用库存</th>\n",
       "      <th>陈氏自提</th>\n",
       "      <th>信忱自提</th>\n",
       "      <th>罗森下单量</th>\n",
       "      <th>实发件数</th>\n",
       "      <th>结存</th>\n",
       "      <th>备注</th>\n",
       "      <th>鲜浩仓剩余</th>\n",
       "      <th>鲜浩仓剩余/箱</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>简爱树莓滑滑100g*3杯*16组</td>\n",
       "      <td>2022-12-27</td>\n",
       "      <td>16.0</td>\n",
       "      <td>组</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>0.687500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>简爱树莓滑滑100g*3杯*16组</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>16.0</td>\n",
       "      <td>组</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>简爱树莓滑滑100g*3杯*16组</td>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>16.0</td>\n",
       "      <td>组</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>简爱树莓滑滑100g*3杯*16组</td>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>16.0</td>\n",
       "      <td>组</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>简爱香蕉滑滑100g*3杯*16组</td>\n",
       "      <td>2022-12-30</td>\n",
       "      <td>16.0</td>\n",
       "      <td>组</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0.062500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>59</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>父爱配方️西梅苹果吸吸酸奶·0%蔗糖100g*6袋*6组</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>6.0</td>\n",
       "      <td>组</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>60</td>\n",
       "      <td>6.970619e+12</td>\n",
       "      <td>父爱配方️西梅苹果吸吸酸奶·0%蔗糖100g*6袋*6组</td>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>6.0</td>\n",
       "      <td>组</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>61</td>\n",
       "      <td>6.974858e+12</td>\n",
       "      <td>OP3N翻来翻去彩虹甜甜圈 106g*12盒</td>\n",
       "      <td>2022-12-24</td>\n",
       "      <td>12.0</td>\n",
       "      <td>盒</td>\n",
       "      <td>39.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39</td>\n",
       "      <td>3.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>62</td>\n",
       "      <td>6.974858e+12</td>\n",
       "      <td>OP3N翻来翻去彩虹甜甜圈 106g*12盒</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>12.0</td>\n",
       "      <td>盒</td>\n",
       "      <td>40.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>196</td>\n",
       "      <td>NaN</td>\n",
       "      <td>196</td>\n",
       "      <td>16.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>合计</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16546.0</td>\n",
       "      <td>650.0</td>\n",
       "      <td>388.0</td>\n",
       "      <td>8491.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1398.0</td>\n",
       "      <td>9889.0</td>\n",
       "      <td>6860</td>\n",
       "      <td>2584</td>\n",
       "      <td>3350.0</td>\n",
       "      <td>3350.0</td>\n",
       "      <td>13399</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4944</td>\n",
       "      <td>394.576389</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    序号          货物编号                          货物名称         批号   转换率   单位  \\\n",
       "0    1  6.970619e+12             简爱树莓滑滑100g*3杯*16组 2022-12-27  16.0    组   \n",
       "1    2  6.970619e+12             简爱树莓滑滑100g*3杯*16组 2022-12-31  16.0    组   \n",
       "2    3  6.970619e+12             简爱树莓滑滑100g*3杯*16组 2023-01-03  16.0    组   \n",
       "3    4  6.970619e+12             简爱树莓滑滑100g*3杯*16组 2023-01-05  16.0    组   \n",
       "4    5  6.970619e+12             简爱香蕉滑滑100g*3杯*16组 2022-12-30  16.0    组   \n",
       "..  ..           ...                           ...        ...   ...  ...   \n",
       "58  59  6.970619e+12  父爱配方️西梅苹果吸吸酸奶·0%蔗糖100g*6袋*6组 2022-12-31   6.0    组   \n",
       "59  60  6.970619e+12  父爱配方️西梅苹果吸吸酸奶·0%蔗糖100g*6袋*6组 2023-01-02   6.0    组   \n",
       "60  61  6.974858e+12        OP3N翻来翻去彩虹甜甜圈 106g*12盒 2022-12-24  12.0    盒   \n",
       "61  62  6.974858e+12        OP3N翻来翻去彩虹甜甜圈 106g*12盒 2022-12-31  12.0    盒   \n",
       "62  合计           NaN                           NaN        NaT   NaN  NaN   \n",
       "\n",
       "         期初  常规产品整数   常规零数    常规小计  ...    旧货小计    可用库存  陈氏自提  信忱自提   罗森下单量  \\\n",
       "0      11.0     NaN    NaN     0.0  ...    11.0    11.0     0     0     NaN   \n",
       "1      12.0     NaN    NaN     0.0  ...    12.0    12.0     0     0     NaN   \n",
       "2      31.0     1.0   15.0    31.0  ...     0.0    31.0     0    31     NaN   \n",
       "3       NaN     NaN    NaN     NaN  ...     NaN     NaN     0     0     NaN   \n",
       "4       1.0     NaN    NaN     0.0  ...     1.0     1.0     0     0     NaN   \n",
       "..      ...     ...    ...     ...  ...     ...     ...   ...   ...     ...   \n",
       "58     12.0     NaN    NaN     0.0  ...    12.0    12.0     0     0     NaN   \n",
       "59     22.0     1.0   10.0    16.0  ...     0.0    16.0     0     0     NaN   \n",
       "60     39.0     3.0    3.0    39.0  ...     0.0    39.0     0     0     NaN   \n",
       "61     40.0    31.0    4.0   376.0  ...     0.0   376.0     0     0   180.0   \n",
       "62  16546.0   650.0  388.0  8491.0  ...  1398.0  9889.0  6860  2584  3350.0   \n",
       "\n",
       "      实发件数     结存  备注  鲜浩仓剩余     鲜浩仓剩余/箱  \n",
       "0      NaN     11 NaN     11    0.687500  \n",
       "1      NaN     12 NaN     12    0.750000  \n",
       "2      NaN     31 NaN      0    0.000000  \n",
       "3      NaN      0 NaN      0    0.000000  \n",
       "4      NaN      1 NaN      1    0.062500  \n",
       "..     ...    ...  ..    ...         ...  \n",
       "58     NaN     12 NaN     12    2.000000  \n",
       "59     NaN     16 NaN     16    2.666667  \n",
       "60     NaN     39 NaN     39    3.250000  \n",
       "61   180.0    196 NaN    196   16.333333  \n",
       "62  3350.0  13399 NaN   4944  394.576389  \n",
       "\n",
       "[63 rows x 22 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "df = pd.read_excel(\"data/data_1.xlsx\",sheet_name= 0)  # sheet name 表示读取第几个sheet\n",
    "df "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69aa6321",
   "metadata": {},
   "source": [
    "#### 遍历获取每一行的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c48c0cc2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "序号                           1\n",
      "货物编号           6970618570001.0\n",
      "货物名称         简爱树莓滑滑100g*3杯*16组\n",
      "批号         2022-12-27 00:00:00\n",
      "转换率                       16.0\n",
      "单位                           组\n",
      "期初                        11.0\n",
      "常规产品整数                     NaN\n",
      "常规零数                       NaN\n",
      "常规小计                       0.0\n",
      "旧货整数                       NaN\n",
      "旧货零数                      11.0\n",
      "旧货小计                      11.0\n",
      "可用库存                      11.0\n",
      "陈氏自提                         0\n",
      "信忱自提                         0\n",
      "罗森下单量                      NaN\n",
      "实发件数                       NaN\n",
      "结存                          11\n",
      "备注                         NaN\n",
      "鲜浩仓剩余                       11\n",
      "鲜浩仓剩余/箱                 0.6875\n",
      "Name: 0, dtype: object\n"
     ]
    }
   ],
   "source": [
    "for row_index,data in df.iterrows():\n",
    "    # row_index 表示是第几行   data 是某一行的数据\n",
    "    print(row_index)\n",
    "    print(data)\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "706458ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 6970618570001.0, '简爱树莓滑滑100g*3杯*16组',\n",
       "       Timestamp('2022-12-27 00:00:00'), 16.0, '组', 11.0, nan, nan, 0.0,\n",
       "       nan, 11.0, 11.0, 11.0, 0, 0, nan, nan, 11, nan, 11, 0.6875],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 这一行的所有值\n",
    "data.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06bd1cb9",
   "metadata": {},
   "source": [
    "这一行的所有键"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1f459915",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['序号', '货物编号', '货物名称', '批号', '转换率', '单位', '期初', '常规产品整数', '常规零数', '常规小计',\n",
       "       '旧货整数', '旧货零数', '旧货小计', '可用库存', '陈氏自提', '信忱自提', '罗森下单量', '实发件数', '结存',\n",
       "       '备注', '鲜浩仓剩余', '鲜浩仓剩余/箱'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "482b7422",
   "metadata": {},
   "source": [
    "根据键名获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fafebd49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"旧货零数\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ee57438",
   "metadata": {},
   "source": [
    "根据键的索引获取值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2b54c1eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'简爱树莓滑滑100g*3杯*16组'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dd8755f",
   "metadata": {},
   "source": [
    "### pandas 生成表格"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37616646",
   "metadata": {},
   "source": [
    "要生产表格的数据 ，二维列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b6358df6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_list = [\n",
    "    [1,2,3],\n",
    "    [4,5,6]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75b9d908",
   "metadata": {},
   "source": [
    "根据二维列表生成pandas dataframe 数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "538058ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c\n",
       "g  1  2  3\n",
       "h  4  5  6"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(data_list,columns=['a','b','c'],index=['g','h'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a0c53d3",
   "metadata": {},
   "source": [
    "保存到本地"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "286c0fdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('1.xlsx',index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28abc9a2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
